package com.etooling.germa.repository;

import com.etooling.framework.repository.BaseRepository;
import com.etooling.germa.entity.ImportBomDetails;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

@Repository
public interface ImportBomDetailsRepository extends BaseRepository<ImportBomDetails> {
    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "select * from import_data.t_import_bom_details where imp_data_bach_id = ?1 ")
    List<ImportBomDetails> getByImportDataBatchId(String id);

    @Query(nativeQuery = true, value = "SELECT COUNT(*) " +
            "FROM import_data.t_import_bom_details " +
            "WHERE imp_data_bach_id = ?1 " +
            "AND TRIM(result_memo) IS NOT NULL " +
            "AND TRIM(result_memo) <> ''")
    Integer getResultMemoCount(String importDataBatchId);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "INSERT INTO import_data.t_import_bom_details (goods_specs_id)" +
            "    SELECT :goodsSpecsId WHERE :goodsSpecsId IS NOT NULL")
    void saveId(@Param("goodsSpecsId") String goodsSpecsId);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "UPDATE import_data.t_import_bom_details " +
            "SET goods_specs_id = :specsId " +
            "WHERE imp_data_bach_id = :importDataBatchId")
    void setGoodsSpecsId(@Param("importDataBatchId") String importDataBatchId,
                         @Param("specsId") String specsId);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "INSERT INTO pdm.t_bom_detail(id, parent_id, company_id,goods_specs_id, level, material_goods_code, material_goods_name, " +
            "material_specs_code, material_specs_name, material_tag, memo, seq, status, unit, unit_num, bom_id) " +
            "SELECT id, parent_id, ?3 company_id,   gs_id as goods_specs_id,level, material_goods_code, material_goods_name, " +
            "material_specs_code, material_specs_name, material_tag, memo, seq, status, unit, unit_num, ?2 bom_id " +
            "FROM (SELECT bd.*, gs.id as gs_id, " +
            "             (SELECT id " +
            "              FROM (SELECT * " +
            "                    FROM import_data.t_import_bom_details " +
            "                    ORDER BY seq DESC) fp " +
            "              WHERE fp.seq < bd.seq " +
            "                AND fp.level < bd.level and  fp.imp_data_bach_id =?1 " +
            "              LIMIT 1) parent_id " +
            "      FROM import_data.t_import_bom_details bd " +
            "      LEFT JOIN pdm.t_goods_specs gs ON bd.material_specs_code = gs.sku_code " +
            "      WHERE imp_data_bach_id = ?1 " +
            "      ORDER BY seq) aa")
    void updateDetails(String importDataBatchId, String bomId, String companyId);

    //有sku
    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "UPDATE pdm.t_bom_detail tbd3 " +
            "SET goods_specs_id = bb.gs_id, sku_code = bb.sku_code " +
            "FROM (SELECT tbd2.goods_specs_id gs_id, tbd2.sku_code, tbd2.material_goods_name, tbd2.material_specs_code " +
            "      FROM pdm.t_bom_detail tbd " +
            "               LEFT JOIN pdm.t_bom_detail tbd2 ON tbd.material_goods_name = tbd2.material_goods_name AND " +
            "                                                  tbd.material_specs_code = tbd2.material_specs_code " +
            "      WHERE tbd.bom_id = ?1 " +
            "        AND tbd2.goods_specs_id IS NOT NULL) bb " +
            "WHERE tbd3.material_goods_name = bb.material_goods_name " +
            "  AND tbd3.material_specs_code = bb.material_specs_code")
    void updateGoodsSpecsId(String bomId);
}